package com.kfm.jdbc.day01;

import java.sql.*;

/**
 * jdbc 操作步骤
 * 导入 jar 包 (一个项目导入一次即可)
 * 1. 加载驱动
 * 2. 创建连接
 * 3. 创建 Statement 对象
 * 4. 执行 SQL
 * 5. 解析结果
 * 6. 释放资源
 *
 * 查询: select 列名1, 列名2, ...  from 表名;
 * 条件查询： select 列名1, 列名2  from 表名 where 条件;
 * 新增： insert into 表名 (列名1, 列名2, ...) values(值1, 值2,, ...);
 * 更新: update 表名 set 列1 = 值1, 列2 = 值2, .... where 条件;
 * 删除: delete from 表名 where 条件;
 *
 */
public class Demo {

    public static void main(String[] args) {
        update();
    }

    private static void update() {
        try {
            // 1. 加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }

        String url = "jdbc:mysql://127.0.0.1:3306/company_info";
        String user = "root";
        String pass = "root";
        try (// 2.创建连接
             Connection connection = DriverManager.getConnection(url, user, pass);
             // 3. 创建 Statement 对象
             Statement statement = connection.createStatement();
        ) {

            // 4. 执行 SQL
            int no = 12;
            String deptName = "售后服务部2";
            String sql = "update dept set deptname = '%s' where deptno = %d".formatted(deptName, no);
            System.out.println(sql);
            // execute 方法执行增删改操作 没有 resultSet
            int rows = statement.executeUpdate(sql);

            if (rows > 0) {
                System.out.println("成功");
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }


    public static void query() {
        try {
            // 1. 加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }

        String url = "jdbc:mysql://127.0.0.1:3306/company_info";
        String user = "root";
        String pass = "root";
        try (// 2.创建连接
             Connection connection = DriverManager.getConnection(url, user, pass);
             // 3. 创建 Statement 对象
             Statement statement = connection.createStatement();
        ) {

            // 4. 执行 SQL
            String sql = "select * from dept";
            boolean success = statement.execute(sql);

            if (success) {
                // 执行成功
                /*
                 5. 解析结果
                   获取结果集对象 ResultSet
                 */
                ResultSet resultSet = statement.getResultSet();
                // 是否有结果
                while (resultSet.next()) {
                    // resultSet 指向当前行的结果, 获取内容

                    int deptNo = resultSet.getInt(1);
                    String deptName = resultSet.getString(2);

                    System.out.println(deptNo + " - " + deptName);
                }

                // 释放资源, 在 finally 或者 try-with-resources 中释放
                resultSet.close();
            }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }


    public static void insert() {
        try {
            // 1. 加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }

        String url = "jdbc:mysql://127.0.0.1:3306/company_info";
        String user = "root";
        String pass = "root";
        try (// 2.创建连接
             Connection connection = DriverManager.getConnection(url, user, pass);
             // 3. 创建 Statement 对象
             Statement statement = connection.createStatement();
        ) {

            // 4. 执行 SQL
            int no = 13;
            String deptName = "售后服务部1";
            String sql = "insert into dept(deptno, deptname) values(%d, '%s')".formatted(no, deptName);
            System.out.println(sql);
            // execute 方法执行增删改操作 没有 resultSet
            statement.execute(sql);

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public static void insert2() {
        try {
            // 1. 加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }

        String url = "jdbc:mysql://127.0.0.1:3306/company_info";
        String user = "root";
        String pass = "root";
        try (// 2.创建连接
             Connection connection = DriverManager.getConnection(url, user, pass);
             // 3. 创建 Statement 对象
             Statement statement = connection.createStatement();
        ) {

            // 4. 执行 SQL
            int no = 14;
            String deptName = "售后服务部2";
            String sql = "insert into dept(deptno, deptname) values(%d, '%s')".formatted(no, deptName);
            System.out.println(sql);
            // execute 方法执行增删改操作 没有 resultSet
            int rows = statement.executeUpdate(sql);

            if (rows > 0) {
                System.out.println("成功");
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public static void delete(){
        try {
            // 1. 加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }

        String url = "jdbc:mysql://127.0.0.1:3306/company_info";
        String user = "root";
        String pass = "root";
        try (// 2.创建连接
             Connection connection = DriverManager.getConnection(url, user, pass);
             // 3. 创建 Statement 对象
             Statement statement = connection.createStatement();
        ) {

            // 4. 执行 SQL
            int deptno = 13;
            String sql = "delete FROM dept where deptno = %d".formatted(deptno);
            System.out.println(sql);
            // execute 方法执行增删改操作 没有 resultSet
            int rows = statement.executeUpdate(sql);

            if (rows > 0) {
                System.out.println("成功");
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}
